package com.wz.excelimport.utils;

import com.wz.excelimport.entity.Excel;
import com.wz.excelimport.entity.ExcelStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.*;

public class WriteExcel {

    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";

    public static void main(String[] args) {

        Map<String, String> dataMap=new HashMap<String, String>();
        dataMap.put("BankName", "BankName");
        dataMap.put("Addr", "Addr");
        dataMap.put("Phone", "Phone");
        List<Map> list=new ArrayList<Map>();
        list.add(dataMap);
        writeExcel(list, 3, "D:/writeExcel.xlsx");

    }

    public static void writeExcel(List<Map> dataList, int cloumnCount,String finalXlsxPath){
        OutputStream out = null;
        try {
            // 获取总列数
            int columnNumCount = cloumnCount;
            // 读取Excel文档
            File finalXlsxFile = new File(finalXlsxPath);
            Workbook workBook = getWorkbok(finalXlsxFile);
            // sheet 对应一个工作页
            Sheet sheet = workBook.getSheetAt(0);
            /**
             * 删除原有数据，除了属性列
             */
            int rowNumber = sheet.getLastRowNum();    // 第一行从0开始算
            System.out.println("原始数据总行数，除属性列：" + rowNumber);
            for (int i = 1; i <= rowNumber; i++) {
                Row row = sheet.getRow(i);
                sheet.removeRow(row);
            }
            // 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out =  new FileOutputStream(finalXlsxPath);
            workBook.write(out);
            /**
             * 往Excel中写新数据
             */
            for (int j = 0; j < dataList.size(); j++) {
                // 创建一行：从第二行开始，跳过属性列
                Row row = sheet.createRow(j + 1);
                // 得到要插入的每一条记录
                Map dataMap = dataList.get(j);
                String openId = dataMap.get("openId").toString();
                String fuName = dataMap.get("fuName").toString();
                String phone = dataMap.get("phone").toString();
                String username = dataMap.get("username").toString();
                String weixinname = dataMap.get("weixinname").toString();
                for (int k = 0; k <= columnNumCount; k++) {
                    // 在一行内循环
                    Cell first = row.createCell(0);
                    first.setCellValue(openId);

                    Cell second = row.createCell(1);
                    second.setCellValue(fuName);

                    Cell third = row.createCell(2);
                    third.setCellValue(phone);

                    Cell four = row.createCell(3);
                    four.setCellValue(username);

                    Cell five = row.createCell(4);
                    five.setCellValue(weixinname);
                }
            }
            // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out =  new FileOutputStream(finalXlsxPath);
            workBook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(out != null){
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        System.out.println("数据导出成功");
    }

    /**
     * 判断Excel的版本,获取Workbook
     * @param in
     * @param filename
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbok(File file) throws IOException {
        Workbook wb = null;
        if (!file.exists()) {
            file.createNewFile();
        }
//        FileInputStream in = new FileInputStream(file);
//        if(file.getName().endsWith(EXCEL_XLS)){     //Excel&nbsp;2003
//            wb = new HSSFWorkbook(in);
//        }else if(file.getName().endsWith(EXCEL_XLSX)){    // Excel 2007/2010
//            wb = new XSSFWorkbook(in);
//        }


        boolean isExcel2003 = true;
        if (file.getName().matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        FileInputStream in = new FileInputStream(file);
        if (isExcel2003) {
            wb = new HSSFWorkbook(in);
        } else {
            wb = new XSSFWorkbook(in);
        }



        return wb;
    }

//
//    /**
//     * 将List转换成Excel.
//     *
//     * @param list 数据集合
//     * @param filePath java7中的文件操作  创建方式:Paths.get("文件地址");
//     * @param fieldMapper
//     * @param headStyle
//     * @param contentStyle
//     * @return void
//     */
//    public static <T> void listToExcel(List<T> list, Path filePath, Map<String, Map<String, String>> fieldMapper,
//                                       ExcelStyle headStyle, ExcelStyle contentStyle) throws Exception {
//        // List为null或者empty抛出异常
//        Optional.ofNullable(list)
//                .filter(l -> !l.isEmpty())
//                .orElseThrow(NullPointerException::new);
//
//        // 1. 获取类的字符串属性
//        List<Excel> excelList = getExcelAnnoList(list.get(0).getClass());
//        // 2. 创建工件薄,工件表
//        Workbook workbook = new SXSSFWorkbook();
//        Sheet sheet = workbook.createSheet();
//        // 3. 设置表头
//        setHead(excelList, sheet, headStyle);
//        // 4. 设置内容
//        setContent(list, sheet, fieldMapper, contentStyle);
//        // 5. 写文件
//        workbook.write(Files.newOutputStream(filePath));
//    }


}
